# coding: utf-8

# 修复日统计数据中，付费用户数等字段上线前为空

import os
import json
import time
import datetime

import set_env_path     # 请勿删除
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def fix_opc_day_stat():
    """
    add by zhouhl 2018-05-15
    """
    # 查询opc_day_stat中的id和stat_date字段
    get_opc_day_stat_sql = text("""
        select id,stat_date from opc_day_stat order by id asc;
    """)

    # 统计付费人数、付费单数、付费总额
    select_pay_cnt_sql = text("""
        select
            count(0) as pay_cnt,
            count(distinct uid) as pay_people_cnt,
            sum(amt) as pay_amt
        from
            bill
        join
            user
        on
            user.id = bill.uid
        where
            bill.type = 1
            and bill.sub_type in (2, 3)
            and bill.status = 200
            and bill.created_at BETWEEN :bill_start and :bill_end
            and bill.deleted_at = 0
            and user.mobile not in (
                '13699899775', '18817877341', '15801577693', '18910632128',
                '18588290314', '18381665312', '18680308092', '13825187899',
                '13421376954', '13430607063', '15603053210', '13424710263',
                '18038064601', '17682342507', '18718925917', '18516234676',
                '15889797200', '13684988236', '13017671158', '18221772171',
                '18682115996', '15166841990', '15674601234', '18229460730'
            );
    """)

    # 更新数据
    update_opc_day_stat_sql = text("""
        update
            opc_day_stat
        set
            pay_people_cnt = :pay_people_cnt,
            pay_cnt = :pay_cnt,
            pay_amt = :pay_amt
        where
            id = :id;
    """)

    stat_data_list = conn.execute(get_opc_day_stat_sql).fetchall()
    for i in stat_data_list:
        print(i.id)
        print(i.stat_date)

        bill_start = int(time.mktime(i.stat_date.timetuple()))
        bill_end = int(time.mktime((i.stat_date + datetime.timedelta(days=1)).timetuple()))

        tmp_result = conn.execute(
            select_pay_cnt_sql,
            bill_start = bill_start,
            bill_end = bill_end
        ).fetchone()

        print(tmp_result.pay_people_cnt)
        print(tmp_result.pay_cnt)
        print(tmp_result.pay_amt)

        conn.execute(
            update_opc_day_stat_sql,
            pay_people_cnt = tmp_result.pay_people_cnt,
            pay_cnt = tmp_result.pay_cnt,
            pay_amt = tmp_result.pay_amt or 0,
            id = i.id
        )
        print('*' * 50)


if __name__ == '__main__':
    fix_opc_day_stat()
